﻿using System;
using System.Data.Odbc;
using System.Diagnostics;
using Persons;

namespace AccessODBC
{
    public class TestAccDB
    {
        private readonly OdbcConnection _odbcConnection;
        private readonly string _connectionString;
        private OdbcCommand _command;

        public TestAccDB(string connectionString)
        {
            _connectionString = connectionString;
            _odbcConnection = new OdbcConnection(_connectionString);
        }

        private bool Open()
        {
            try
            {
                _odbcConnection.Open();
                return true;
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return false;
            }
        }

        private bool Close()
        {
            try
            {
                _odbcConnection.Close();
                return true;
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return false;
            }
        }

        public void Query01()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query01 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT FirstName, LastName FROM Employee", _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nFirst query results");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("First name: {0} \t Last Name: {1}", odbcDateReader[0], odbcDateReader[1]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;}
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query01 couldn't be served");
        }
        
        public void Query02()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query02 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT DISTINCT FirstName FROM Employee ORDER BY FirstName", _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nSecond query results");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("First name: {0}", odbcDateReader[0]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;}
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query02 couldn't be served");
        }

        public void Query03()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query03 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT COUNT(fk_Departments_Id) AS Employees FROM Employee", _odbcConnection);
                Console.WriteLine("\nThird query results: Count = {0}", (int)_command.ExecuteScalar());
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query03 couldn't be served");
        }

        public void Query04()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query04 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT Departments.Id, Count(Employee.fk_Departments_Id) " + 
                    "FROM Departments INNER JOIN Employee ON Departments.Id=Employee.fk_Departments_Id " + 
                    "GROUP BY Departments.Id " + 
                    "ORDER BY Count(Employee.fk_Departments_Id) DESC", _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nFourth query results");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("Department Id: {0} \t Employees count: {1}", odbcDateReader[0], odbcDateReader[1]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query04 couldn't be served");
        }

        public void Query05()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query05 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT Departments.Id AS Department, Count(Employee.fk_Departments_Id) AS Employees " + 
                    "FROM Departments INNER JOIN Employee ON Departments.Id = Employee.fk_Departments_Id " + 
                    "GROUP BY Departments.Id, Departments.City " + 
                    "ORDER BY Departments.City DESC", _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nFifth query results");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("Department Id: {0} \t Employees count: {1}", odbcDateReader[0], odbcDateReader[1]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query05 couldn't be served");
        }

        public void Query06()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query06 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT Departments.City " + 
                    "FROM Departments " +
                    "WHERE Departments.City Like '" + Convert.ToChar(1051) + "*'", _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nSixth query results: ");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("Department city: {0}", odbcDateReader[0]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query06 couldn't be served");
        }

        public void Query07()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query07 couldn't be served");
                return;
            }
            try
            {
                var query = "SELECT Departments.City AS Cities, Count(Employee.fk_Departments_Id) AS Employees " +
                    "FROM Departments LEFT JOIN Employee ON Departments.Id=Employee.fk_Departments_Id " +
                    "WHERE Departments.City LIKE '?*" + Convert.ToChar(1100) + "?*'" +
                    "GROUP BY Departments.City";
                _command = new OdbcCommand(query, _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\nSeventh query results: ");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("Department city: {0} \t Count: {1}", odbcDateReader[0], odbcDateReader[1]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query07 couldn't be served");
        }

        public void Query08()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query08 couldn't be served");
                return;
            }
            try
            {
                var query = "SELECT LastName, FirstName FROM Employee " + 
                    "WHERE ((LastName In (SELECT LastName FROM Employee As Tmp GROUP BY LastName HAVING Count(*)>1 ))) " +
                    "ORDER BY LastName";
                _command = new OdbcCommand(query, _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\n8th query results: ");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("Last Name: {0} \t First Name: {1}", odbcDateReader[0], odbcDateReader[1]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query08 couldn't be served");
        }

        public void Query09()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query09 couldn't be served");
                return;
            }
            try
            {
                var query = "SELECT First(Employee.FirstName), First(Employee.LastName), Count(Employee.FirstName) "+ 
                    "FROM Departments INNER JOIN Employee ON Departments.Id = Employee.fk_Departments_Id " + 
                    "GROUP BY Employee.FirstName, Employee.LastName, Departments.City " +  
                    "HAVING ((Count(Employee.FirstName)>1) And (Count(Employee.LastName)>1) And (Departments.City='Львов'))" + 
                    "ORDER BY Employee.FirstName";
                _command = new OdbcCommand(query, _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\n9th query results: ");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("First Name: {0} \t Last Name: {1} \t Count: {2}", odbcDateReader[0], odbcDateReader[1], odbcDateReader[2]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query09 couldn't be served");
        }

        public void Query10()
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query10 couldn't be served");
                return;
            }
            try
            {
                var query = "SELECT Departments.City, Employee.FirstName, Count(Employee.FirstName) " +
                    "FROM Departments INNER JOIN Employee ON Departments.Id = Employee.fk_Departments_Id " +
                    "WHERE Employee.FirstName='Иван' " +
                    "GROUP BY Departments.City, Departments.Id, Employee.FirstName " +
                    "HAVING Count(Employee.FirstName)>10 " +
                    "ORDER BY Departments.Id";
                _command = new OdbcCommand(query, _odbcConnection);
                var odbcDateReader = _command.ExecuteReader();
                Console.WriteLine("\n10th query results: ");
                while (odbcDateReader.Read())
                {
                    Console.WriteLine("City: {0} \t First Name: {1} \t Count: {2}", odbcDateReader[0], odbcDateReader[1], odbcDateReader[2]);
                }
                odbcDateReader.Close();
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query10 couldn't be served");
        }

        public void Query11(string cityName, Person person1, Person person2, Person person3)
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query11 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("INSERT INTO Departments(City) VALUES(?)", _odbcConnection);
                _command.Parameters.AddWithValue("@p1", cityName);
                var rowsAffected = _command.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    _command = new OdbcCommand("SELECT Id FROM Departments WHERE City = '" + cityName + "'", _odbcConnection);
                    var cityNameId = (int) _command.ExecuteScalar();

                    _command = new OdbcCommand("INSERT INTO Employee(FirstName, Lastname, fk_Departments_Id) " + 
                        "VALUES(?, ?, ?)", _odbcConnection);
                    _command.Parameters.AddWithValue("@p1", person1.FirstName);
                    _command.Parameters.AddWithValue("@p2", person1.LastName);
                    _command.Parameters.AddWithValue("@p3", cityNameId);
                    //_command.Parameters.Add("@p3", OdbcType.Int).Value = cityNameId;
                    rowsAffected = _command.ExecuteNonQuery();

                    _command = new OdbcCommand("INSERT INTO Employee(FirstName, Lastname, fk_Departments_Id) " +
                        "VALUES(?, ?, ?)", _odbcConnection);
                    _command.Parameters.AddWithValue("@p1", person2.FirstName);
                    _command.Parameters.AddWithValue("@p2", person2.LastName);
                    _command.Parameters.AddWithValue("@p3", cityNameId);
                    //_command.Parameters.Add("@p3", OdbcType.Int).Value = cityNameId;
                    rowsAffected = _command.ExecuteNonQuery();

                    _command = new OdbcCommand("INSERT INTO Employee(FirstName, Lastname, fk_Departments_Id) " +
                        "VALUES(?, ?, ?)", _odbcConnection);
                    _command.Parameters.AddWithValue("@p1", person3.FirstName);
                    _command.Parameters.AddWithValue("@p2", person3.LastName);
                    _command.Parameters.AddWithValue("@p3", cityNameId);
                    //_command.Parameters.Add("@p3", OdbcType.Int).Value = cityNameId;
                    rowsAffected = _command.ExecuteNonQuery();

                    Console.WriteLine("\nEleventh query results: added with id = {0}", cityNameId);
                }
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query11 couldn't be served");
        }

        public void Query12(string cityName)
        {
            if (!Open())
            {
                Trace.TraceWarning(DateTime.Now + " --> " + "Query12 couldn't be served");
                return;
            }
            try
            {
                _command = new OdbcCommand("SELECT Id FROM Departments WHERE City = ?", _odbcConnection);
                _command.Parameters.AddWithValue("@p1", cityName);
                var cityNameId = _command.ExecuteScalar();
                if (cityNameId != null)
                {
                    Console.WriteLine("\nCity({0}) Id = {1}", cityName, (int)cityNameId);
                    _command = new OdbcCommand("DELETE FROM Employee WHERE fk_Departments_Id = ?", _odbcConnection);
                    _command.Parameters.AddWithValue("@p1", (int)cityNameId);
                    var rowsAffected = _command.ExecuteNonQuery();
                    Console.WriteLine("\nDeleted rows in Employee(city id {0}) = {1}", (int)cityNameId, rowsAffected);
                    _command = new OdbcCommand("DELETE FROM Departments WHERE Id = ?", _odbcConnection);
                    _command.Parameters.AddWithValue("@p1", (int)cityNameId);
                    rowsAffected = _command.ExecuteNonQuery();
                    Console.WriteLine("\nDeleted rows in Departments(city id {0}) = {1}", (int)cityNameId, rowsAffected);
                }
                else
                {
                    Trace.TraceInformation(DateTime.Now + " --> " + "City(" + cityName + ") is not present in DB");
                }
            }
            catch (Exception e)
            {
                Trace.TraceError(DateTime.Now + " --> " + e.Message);
                return;
            }
            if (Close()) return;
            Trace.TraceWarning(DateTime.Now + " --> " + "Query12 couldn't be served");
        }
    }
}
